clear
set more off

********************************************************************************
***** Project: The Short and Long Term Effects of In-Person Performance Feedback
********************************************************************************
***** A. R. Soetevent & G. J. Romensen
********************************************************************************
***** Cleaning the Database
********************************************************************************
***** Latest update: 06-09-2021
********************************************************************************
*global filepath "C:\JPEMicReplication"
log using "$filepath/Logs/01Cleaning.log", replace



********************************************************************************
** DATA Friesland 
********************************************************************************
use "$filepath\Data\Raw data\rawtripdata_1501 tm 1701.dta"

*** 1. Make all variables lower case

foreach var of varlist _all {
rename `var' `=lower("`var'")'
}

*** 2. Drop duplicates

duplicates report
duplicates drop

*** 3. Drop if used bus cannot be identified 
* 05.11.21 But, first check whether bus identifiers are randomly missing!
gen byte busidmissing = 0 
replace busidmissing = 1 if wagenpark_nr_da==. & wagenpark_nr_dp==. 

tabulate treatment, generate(TR)

tab ecobox_bustype if busidmissing==1
*VOERTUIG_OMSCHRIJVING			Freq.	Percent	Cum.		VDL or Intouro		
*DAF MB200						100		0.04	0.04	1626			
*DAF SB200 AMBASSADOR			1,524	0.61	0.65				
*DAF SB200 COMMANDER			2	0	0.65				
*Electr 12m (in bestelling)		3	0	0.65			3			
*IRISBUS CITELIS 10,5 M			29,413	11.81	12.46	117,023			
*IRISBUS CITELIS 10,5 M CNG		17,587	7.06	19.52				
*IRISBUS CITELIS 12 M			28,917	11.61	31.13				
*IRISBUS CITELIS 12 M CNG		18,789	7.54	38.68				
*Irisbus Citelis 10,5 meter CNG	9,085	3.65	42.32				
*Irisbus Citelis 12 meter CNG	13,232	5.31	47.64				
*Jonckheere HOCL-16.290			1	0	47.64			29702			
*MAN  Marco Polo				3	0	47.64				
*MAN LIONS CITY GELEED CNG		29,697	11.92	59.56				
*MAN Marco Polo					1	0	59.56				
*MB Intouro	196					0.08	59.64	567		96,326		0.331316619
*MB Intouro (Qbuzz)				371	0.15	59.79				
*MB Sprinter (ATBS)				7	0	59.79			7			
*MERCEDES BENZ INTOURO			7,676	3.08	62.87	7,676			
*MERCEDES-BENZ INTEGRO QLINER	1,116	0.45	63.32	4,241			
*Mercedes Benz O405 (KROL)		1	0	63.32				
*NTB							3	0	63.32				
*SCANIA OMNILINK				1,390	0.56	63.88				
*SCANIA OMNILINK GELEED			1,731	0.69	64.57				
*VDL AMBASSADOR ALE 106			15,132	6.08	70.65	88,083			
*VDL CITEA LLE 120				66,795	26.82	97.47				
*VDL CITEA LLE EURO 6			497	0.2	97.67				
*VDL CITEA XLE 145				529	0.21	97.88				
*VDL Citea LLE					4,657	1.87	99.75				
*VDL Citea LLE 120				463	0.19	99.93				
*VDL Citea SLF					5	0	99.93				
*VDL Citea SLF-E				5	0	99.94				
*VOLVO 8900 LE 13M				25	0.01	99.95		157			
*Volvo 8908RLE					39	0.02	99.96				
*Volvo B12B (467-Arriva Touring)	93	0.04	100				
							
*Total							249,085	100					


corr busidmissing TR2 TR3 TR4 if (ecobox_bustype=="VDL" | ecobox_bustype=="INTOURO")


drop if wagenpark_nr_da==. & wagenpark_nr_dp==. 
drop busidmissing 
*** 4. Drop bus types without an ecobox

tab voertuig_omschrijving if ecobox_bustype=="VDL" | ecobox_bustype=="INTOURO" | ecobox_bustype=="IRISBUS"
levelsof voertuig_omschrijving if ecobox_bustype=="VDL" | ecobox_bustype=="INTOURO" | ecobox_bustype=="IRISBUS", local(level_ecobox)

gen ecobox=0
foreach var of local level_ecobox {
replace ecobox=1 if voertuig_omschrijving=="`var'"
}

drop if ecobox==0
drop ecobox

*** 5. Further inspection of bus types

tab voertuig_omschrijving 
	
* CNG = gas. No CNG means diesel for Iris bus

replace voertuig_omschrijving="IRISBUS CITELIS 10,5 M CNG" if voertuig_omschrijving=="Irisbus Citelis 10,5 meter CNG"
replace voertuig_omschrijving="IRISBUS CITELIS 12 M CNG" if voertuig_omschrijving=="Irisbus Citelis 12 meter CNG"
replace voertuig_omschrijving="VDL CITEA LLE 120" if voertuig_omschrijving=="VDL Citea LLE 120" | voertuig_omschrijving=="VDL Citea LLE"
replace voertuig_omschrijving="MERCEDES BENZ INTOURO" if voertuig_omschrijving=="MB Intouro (Qbuzz)" | voertuig_omschrijving=="MB Intouro"

tab voertuig_omschrijving 

*** 6. Dealing with error messages from board computer

tab m_rituitval_ind_da 
tab m_rituitval_ind_dp

* da = albatros boordcomputer.
* dp = Ecobox.
* E02: verbruik buiten grens bustype (determined by Wouter).
* U02: bustype niet herkend door boordcomputer.
* U01: no da in iris bus therefore error notification.

***********

gen verbruik_threshold=cond(strpos(m_rituitval_ind_da, "E02"),1,0)

replace m_rituitval_ind_dp="" if m_rituitval_ind_dp=="."
replace m_rituitval_ind_da="" if m_rituitval_ind_da==":XXX"
replace m_rituitval_ind_dp="" if m_rituitval_ind_dp==":XXX"

tab m_rituitval_ind_da
tab m_rituitval_ind_dp

drop if m_rituitval_ind_da!="" & m_rituitval_ind_da!=":U01" & verbruik_threshold==0
drop if m_rituitval_ind_dp!=""

drop verbruik_threshold

*** 7. Within-driver obs. with the same "geplande_begintijd_rit"

gen hulp=0
replace hulp=1 if datum>=date("1-11-2016", "DMY") & datum<=date("31-11-2016", "DMY")

bysort chauf_nr_rug datum geplande_begintijd_rit: gen sametime=cond(_N==1,0,_n) if geplande_begintijd_rit!=.
drop if sametime>=1 & sametime!=. & hulp==0
drop if hulp==1 & sametime>=1 & sametime!=. & plaatsnaam_begin==""
drop sametime
drop hulp

*** 8. Drop very short rides 

drop if rijden_afstand<1 | rijden_afstand_dp<1

*** 9. Fuel economy

replace verbruik_calc=. if strpos(voertuig_omschrijving, "IRISBUS")

gen fuel_economy=rijden_afstand/verbruik_calc

* Drop unreasonable fuel_economy figures

drop if (fuel_economy<1 | fuel_economy>8) & fuel_economy!=.

hist fuel_economy

*** 10. Punctuality

gen datehulp=dofc(geplande_begintijd_rit)
gen datehulp2=dofc(geplande_eindtijd_rit)
gen datehulp3=dofc(werkelijke_begintijd_rit)
gen datehulp4=dofc(werkelijke_eindtijd_rit)

format datehulp %td
format datehulp2 %td
format datehulp3 %td
format datehulp4 %td

* CORRECTION 03.08.2023: conditions now account for missing departure times
* flag = 1 if planned date of arrival is before planned date of departure
gen flag=cond((datehulp>datehulp2 & datehulp~=.) ,1,0)
* flag2 = 1 if actual date of arrival is before actual date of departure  
gen flag2=cond((datehulp3>datehulp4 & datehulp3~=.),1,0)
* flag3 = 1 if planned date of arrival is before planned date of departure AND planned arrival time is before planned departure time
gen flag3=cond(geplande_eindtijd_rit<geplande_begintijd_rit & flag==0,1,0)
* flag4 = 1 if actual date of arrival is before actual date of departure AND actual arrival time is before actual departure time
gen flag4=cond(werkelijke_eindtijd_rit<werkelijke_begintijd_rit & flag2==0,1,0)

tab flag
tab flag2
tab flag3
tab flag4

gen hulp1=geplande_begintijd_rit
gen hulp2=geplande_eindtijd_rit
gen hulp3=werkelijke_begintijd_rit
gen hulp4=werkelijke_eindtijd_rit

* list entries with incorrect planned starting date and correct
list geplande_begintijd_rit geplande_eindtijd_rit if flag==1
replace geplande_begintijd_rit=geplande_begintijd_rit-24*60*60*1000 if flag==1

* list entries with incorrect actual starting date and correct
list werkelijke_begintijd_rit werkelijke_eindtijd_rit if flag2==1
replace werkelijke_begintijd_rit=hulp4 if flag2==1
replace werkelijke_eindtijd_rit=hulp3 if flag2==1

list werkelijke_begintijd_rit werkelijke_eindtijd_rit if flag4==1
replace werkelijke_begintijd_rit=hulp4 if flag4==1
replace werkelijke_eindtijd_rit=hulp3 if flag4==1

gen dif=datehulp2-datehulp
gen dif2=datehulp4-datehulp3
tab dif
tab dif2
replace geplande_eindtijd_rit=geplande_eindtijd_rit-24*60*60*1000 if dif==2

drop flag* datehulp* hulp* dif*

gen punctuality=((((werkelijke_eindtijd_rit - werkelijke_begintijd_rit)-(geplande_eindtijd_rit - geplande_begintijd_rit))/1000)/60)
* Punctuality is in minutes.

* Drop observations with punctuality more than 1 hour off
drop if punctuality>60 & punctuality!=.
drop if punctuality<-60

hist punctuality

* Leaving and arriving late
gen leaveLate = ((werkelijke_begintijd_rit - geplande_begintijd_rit)/1000)/60
label variable leaveLate "No. of minutes driver STARTS trip later than planned departure time"
hist leaveLate, width(1)

gen arriveLate = ((werkelijke_eindtijd_rit - geplande_eindtijd_rit)/1000)/60
label variable arriveLate "No. of minutes driver ENDS trip later than planned arrival time"

hist arriveLate, width(1)

tabstat arriveLate leaveLate, s(p1 p5 p10 p50 p90 p95 p99)

* Ddrop trips that start or end more than 30 minute BEFORE or AFTER planned time.
replace leaveLate = . if   abs(leaveLate)> 30
replace arriveLate = . if  abs(arriveLate)> 30


*** 11. ABC comfort dimensions
local comfort "acceleratie bochten rem"

gen bustype=.
replace bustype=1 if strpos(voertuig_omschrijving, "VDL")
replace bustype=2 if strpos(voertuig_omschrijving, "INTOURO")
replace bustype=3 if strpos(voertuig_omschrijving, "IRISBUS")

replace acceleratie_score=. if datum<date("9-11-2015", "DMY") & bustype==2
replace rem_score=. if datum<date("9-11-2015", "DMY") & bustype==2
replace bochten_score=. if datum<date("9-11-2015", "DMY") & bustype==3

foreach var of local comfort {
gen dep_`var'=10*(`var'_score/rijden_afstand_dp) if datum>=date("1-9-2015", "DMY")
}

foreach var of local comfort {
sum dep_`var'
local `var'mean=r(mean)
local `var'sd=r(sd)
drop if dep_`var'>``var'mean'+5*``var'sd' & dep_`var'!=.
}

*** 12. Sorting the data

sort chauf_nr_rug datum

*** 13. Save cleaned database

save "$filepath\ConstructedData\cleaned_tripdata_1501 tm 1701.dta", replace

*****************************************************************************
***  [Table F6: Fleet information]
*****************************************************************************
tab voertuig_omschrijving



********************************************************************************
** Same procedure: DATA Zuid-Holland
********************************************************************************
clear all

use "$filepath\Data\Raw data\merged_ZHNDAV_rawtripdata.dta"

/*** 0. Preliminaries ***/

* No outcome data available before 1 July 2015.
drop if datum<date("01-07-2015", "DMY")

local hulp "rijden_verbruik rijden_afstand stationair_verbruik stationair_kort_verbruik verbruik_calc rijden_afstand_dp afstand_dp_acceleratie afstand_dp_remmen afstand_dp_bochten afstand_da verbruik_calc_ok"

foreach var of local hulp{
replace `var'=subinstr(`var',",",".",.)
destring `var', replace
}

local hulp2 "geplande_begintijd_rit geplande_eindtijd_rit werkelijke_begintijd_rit werkelijke_eindtijd_rit"

foreach var of local hulp2 {
gen double `var'2=clock(`var', "YMDhms")
format `var'2 %tc
drop `var'
rename `var'2 `var'
}

order driverid datum gender birthyear employmentyear fte coachidentifier coachdatum_* geplande_begintijd_rit geplande_eindtijd_rit werkelijke_*

/*** 1. Drop duplicates ***/

duplicates report
duplicates drop

/*** 2. Drop if used bus cannot be identified ***/

drop if wagenpark_nr_da==. & wagenpark_nr_dp==.

/*** 3. Inspection of bus types ***/

tab voertuig_omschrijving
tab ecobox_bustype

replace voertuig_omschrijving="VDL CITEA LLE 120" if voertuig_omschrijving=="VDL Citea LLE 120" | voertuig_omschrijving=="VDL CITEA LLE 120"

tab voertuig_omschrijving if verbruik_calc==.
* No bus types that operate solely on electricity and/or gas
* All bus types have EOBRs installed

/*** 4. Dealing with error messages from board computer ***/

tab m_rituitval_ind_da 
tab m_rituitval_ind_dp

* da = Albatros board computer
* dp = Ecobox
** Error messages from the bus company's system:
* E01: weird observations with, among others, out of range trip distances and trip times
* E02: fuel use is outside range for bustype (determined by Arriva)
* U04: no information on distance, time and fuel use (value set to 0)
* :XXX: no dissenting pattern (hence not remove)

replace m_rituitval_ind_da="" if m_rituitval_ind_da==":XXX"
replace m_rituitval_ind_dp="" if m_rituitval_ind_dp==":XXX"

drop if m_rituitval_ind_da!="" & strpos(m_rituitval_ind_da, ":E02")==0
drop if m_rituitval_ind_dp!="" 

tab m_rituitval_ind_da
tab m_rituitval_ind_dp

/*** 5. Within-driver obs. with the same "geplande_begintijd_rit" ***/

bysort driverid datum geplande_begintijd_rit: gen sametime=cond(_N==1,0,_n) if geplande_begintijd_rit!=.

tab sametime

egen hulpsametime=group(geplande_ritafstand geplande_begintijd_rit) if sametime>=1 & sametime!=.

bysort hulpsametime: drop if heen_van=="" & heen_naar=="" & sametime>=1 & sametime!=.
bysort hulpsametime: drop if wagenpark_nr_da!=wagenpark_nr_dp & sametime>=1 & sametime!=.

drop sametime
bysort driverid datum geplande_begintijd_rit: gen sametime=cond(_N==1,0,_n) if geplande_begintijd_rit!=.

tab sametime

* Few remaining cases: rides with same departure time
* Drop these b/c not clear if obs. can be attributed to driverid.

drop if sametime>=1 & sametime!=.

tab sametime

drop sametime hulp*

/*** 6. Drop very short rides ***/

drop if rijden_afstand<1 | rijden_afstand_dp<1

/*** 7. Punctuality ***/

gen datehulp=dofc(geplande_begintijd_rit)
gen datehulp2=dofc(geplande_eindtijd_rit)
gen datehulp3=dofc(werkelijke_begintijd_rit)
gen datehulp4=dofc(werkelijke_eindtijd_rit)

format datehulp %td
format datehulp2 %td
format datehulp3 %td
format datehulp4 %td

* flag = 1 if planned date of arrival is before planned date of departure
gen flag=cond((datehulp>datehulp2 & datehulp~=.) ,1,0)
* flag2 = 1 if actual date of arrival is before actual date of departure  
gen flag2=cond((datehulp3>datehulp4 & datehulp3~=.),1,0)
* flag3 = 1 if planned date of arrival is before planned date of departure AND planned arrival time is before planned departure time
gen flag3=cond(geplande_eindtijd_rit<geplande_begintijd_rit & flag==0,1,0)
* flag4 = 1 if actual date of arrival is before actual date of departure AND actual arrival time is before actual departure time
gen flag4=cond(werkelijke_eindtijd_rit<werkelijke_begintijd_rit & flag2==0,1,0)


tab flag
tab flag2
tab flag3
tab flag4

gen double hulp1=geplande_begintijd_rit
gen double hulp2=geplande_eindtijd_rit
gen double hulp3=werkelijke_begintijd_rit
gen double hulp4=werkelijke_eindtijd_rit

foreach x of numlist 1(1)4 {
format hulp`x' %tc
}

list geplande_begintijd_rit geplande_eindtijd_rit if flag==1
* OK

list geplande_begintijd_rit geplande_eindtijd_rit werkelijke_begintijd_rit werkelijke_eindtijd_rit  if flag2==1
replace werkelijke_eindtijd_rit=werkelijke_eindtijd_rit+24*60*60*1000 if flag2==1

replace werkelijke_begintijd_rit=. if geplande_begintijd_rit==. & flag2==1
replace werkelijke_eindtijd_rit=. if geplande_eindtijd_rit==. & flag2==1
list geplande_begintijd_rit geplande_eindtijd_rit werkelijke_begintijd_rit werkelijke_eindtijd_rit  if flag2==1
list geplande_begintijd_rit geplande_eindtijd_rit werkelijke_begintijd_rit werkelijke_eindtijd_rit  if flag2==1 & datehulp!=datehulp3

replace werkelijke_begintijd_rit=werkelijke_begintijd_rit-24*60*60*1000 if flag2==1 & datehulp!=datehulp3
replace werkelijke_eindtijd_rit=werkelijke_eindtijd_rit-24*60*60*1000 if flag2==1 & datehulp!=datehulp3
list geplande_begintijd_rit geplande_eindtijd_rit werkelijke_begintijd_rit werkelijke_eindtijd_rit  if flag2==1 & datehulp!=datehulp3
list geplande_begintijd_rit geplande_eindtijd_rit werkelijke_begintijd_rit werkelijke_eindtijd_rit  if flag2==1 
* OK

tab flag3
* OK

tab flag4

list geplande_begintijd_rit geplande_eindtijd_rit werkelijke_begintijd_rit werkelijke_eindtijd_rit if flag4==1
* These particular "werkelijke_eindtijd_rit" observations are abnormal. 
replace werkelijke_eindtijd_rit=. if flag4==.

gen dif=datehulp2-datehulp
gen dif2=datehulp4-datehulp3
tab dif
tab dif2

* Dif seems OK

list werkelijke_begintijd_rit werkelijke_eindtijd datehulp4 datehulp3 if dif2==-1
* Problem is solved after having dealt with flag2. OK!

drop flag* datehulp* hulp* dif*

gen punctuality=((((werkelijke_eindtijd_rit - werkelijke_begintijd_rit)-(geplande_eindtijd_rit - geplande_begintijd_rit))/1000)/60)
* Punctuality is in minutes.

* drop unreasonable punctuality [1 hour difference]
drop if punctuality>60 & punctuality!=.
drop if punctuality<-60

hist punctuality

* Added 03.08.23:
gen leaveLate = ((werkelijke_begintijd_rit - geplande_begintijd_rit)/1000)/60
label variable leaveLate "No. of minutes driver STARTS trip later than planned departure time"
hist leaveLate, width(1)

* Added 03.08.23:
gen arriveLate = ((werkelijke_eindtijd_rit - geplande_eindtijd_rit)/1000)/60
label variable arriveLate "No. of minutes driver ENDS trip later than planned arrival time"
hist arriveLate, width(1)

tabstat arriveLate leaveLate, s(p1 p5 p10 p50 p90 p95 p99)

* drop unreasonable late leaves and arrival [if trip starts or end more than 30 minute BEFORE or AFTER planned time]
replace leaveLate = . if   abs(leaveLate)> 30
replace arriveLate = . if abs(arriveLate)> 30


/*** 8. Fuel economy ***/

gen fuel_economy=100*(verbruik_calc/rijden_afstand)

* Drop unreasonable fuel_economy figures

drop if (fuel_economy<12.5 | fuel_economy>100) & fuel_economy!=.

/*** 9. ABC dimensions ***/ 


local comfort "acceleratie bochten rem"

foreach var of local comfort {
gen dep_`var'=10*(`var'_score/rijden_afstand_dp)
}

foreach var of local comfort {
sum dep_`var'
local `var'mean=r(mean)
local `var'sd=r(sd)
drop if dep_`var'>``var'mean'+5*``var'sd' & dep_`var'!=.
}

*** 10 Generate bustype variables
tab voertuig_omschrijving
gen bustype=1 if strpos(voertuig_omschrijving, "VDL")
replace bustype=4 if strpos(voertuig_omschrijving, "DAF")
replace bustype=5 if strpos(voertuig_omschrijving, "VOLVO") | strpos(voertuig_omschrijving, "Volvo")
replace bustype=6 if strpos(voertuig_omschrijving, "7700")

/*** 11. Remove redundant variables ***/

drop traject 

/*** 12. Sort the data ***/

sort driverid datum

tab voertuig_omschrijving

/*** 13. Saving cleaned database ***/

save "$filepath\ConstructedData\cleaned_ZHDAV_1507 tm 1712.dta", replace

log close